package com.cardone.common.util;

import java.io.File;
import java.io.IOException;
import java.io.InputStream;
import java.math.BigDecimal;
import java.net.URLEncoder;
import java.util.Calendar;
import java.util.Date;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;

import javax.servlet.http.HttpServletResponse;

import lombok.Getter;
import lombok.Setter;

import org.apache.commons.collections.CollectionUtils;
import org.apache.commons.lang3.ArrayUtils;
import org.apache.commons.lang3.BooleanUtils;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import com.cardone.context.action.Run1Action;
import com.cardone.context.action.Run3Action;
import com.cardone.context.function.Execution1Function;
import com.cardone.context.function.Execution3Function;
import com.google.common.collect.Lists;
import com.google.common.collect.Maps;

/**
 * excel工具类
 *
 * @author yaohaitao
 *
 */
public class ExcelUtils {
	/**
	 * excel配置
	 *
	 * @author yaohaitao
	 *
	 */
	@Getter
	@Setter
	public static class ExcelConfig {
		/**
		 * 实例化
		 *
		 * @param columns
		 *          列定义
		 * @return excel配置
		 */
		public static ExcelConfig newExcelConfig(final Map<Integer, String> columns) {
			final ExcelConfig excelConfig = new ExcelConfig();

			excelConfig.setColumns(columns);

			return excelConfig;
		}

		/**
		 * 动作
		 */
		private Run1Action<Sheet> action;

		/**
		 * 列定义
		 */
		private Map<Integer, String> columns;

		/**
		 * 数据集合
		 */
		private List<Map<String, Object>> dataList;

		/**
		 * 错误数据集合
		 */
		private List<Map<String, Object>> errorDataList;

		/**
		 * 开始行索引
		 */
		private int firstRowIndex;

		/**
		 * 读取行方法
		 */
		private Execution3Function<Map<String, Object>, Row, Integer, Map<Integer, String>> readRowFunction;

		/**
		 * sheet索引集合
		 */
		int[] sheetIndexs = { 0 };

		/**
		 * sheet名称集合
		 */
		String[] sheetNames;

		/**
		 * 成功数据集合
		 */
		private List<Map<String, Object>> succeedDataList;

		/**
		 * 验证方法
		 */
		private Execution1Function<Boolean, Map<String, Object>> validateData;

		/**
		 * 写入动作
		 */
		private Run3Action<Map<String, Object>, Row, Integer> writeRowAction;

		/**
		 * 禁止实例化
		 */
		private ExcelConfig() {
		}
	}

	/**
	 * 复制列
	 *
	 * @param templateCell
	 *          模板列
	 * @param cell
	 *          列
	 */
	private static void copyCell(final Cell templateCell, final Cell cell) {
		cell.getCellStyle().cloneStyleFrom(templateCell.getCellStyle());

		if (templateCell.getCellComment() != null) {
			cell.setCellComment(templateCell.getCellComment());
		}

		final int srcCellType = templateCell.getCellType();

		cell.setCellType(srcCellType);

		if (srcCellType == Cell.CELL_TYPE_NUMERIC) {
			if (DateUtil.isCellDateFormatted(templateCell)) {
				cell.setCellValue(templateCell.getDateCellValue());
			} else {
				cell.setCellValue(templateCell.getNumericCellValue());
			}
		} else if (srcCellType == Cell.CELL_TYPE_STRING) {
			cell.setCellValue(templateCell.getRichStringCellValue());
		} else if (srcCellType == Cell.CELL_TYPE_BOOLEAN) {
			cell.setCellValue(templateCell.getBooleanCellValue());
		} else if (srcCellType == Cell.CELL_TYPE_ERROR) {
			cell.setCellErrorValue(templateCell.getErrorCellValue());
		} else if (srcCellType == Cell.CELL_TYPE_FORMULA) {
			cell.setCellFormula(templateCell.getCellFormula());
		}
	}

	/**
	 * 复制Sheet
	 *
	 * @param templateSheet
	 *          模板sheet
	 * @param sheet
	 */
	private static void copySheet(final Sheet templateSheet, final Sheet sheet) {
		for (int i = 0; i < templateSheet.getLastRowNum(); i++) {
			final Row templateRow = templateSheet.getRow(i);

			if (templateRow == null) {
				continue;
			}

			final Row newRow = sheet.createRow(i);

			for (int j = 0; j < templateRow.getLastCellNum(); j++) {
				final Cell templateCell = templateRow.getCell((short) j);

				if (templateCell == null) {
					continue;
				}

				final Cell newCell = newRow.createCell((short) j);

				newCell.setCellStyle(sheet.getWorkbook().createCellStyle());

				ExcelUtils.copyCell(templateCell, newCell);
			}
		}
	}

	/**
	 * 读取文件
	 *
	 * @param file
	 *          文件
	 * @param excelConfigList
	 *          excel配置集合
	 * @throws InvalidFormatException
	 * @throws IOException
	 * @throws InstantiationException
	 * @throws IllegalAccessException
	 */
	public static void readFile(final File file, final List<ExcelConfig> excelConfigList) throws InvalidFormatException, IOException, InstantiationException, IllegalAccessException {
		final Workbook workbook = WorkbookFactory.create(file);

		ExcelUtils.readWorkbookForExcelConfigs(workbook, excelConfigList);
	}

	/**
	 * 读取文件名
	 *
	 * @param filename
	 *          文件名
	 * @param excelConfigList
	 *          excel配置集合
	 * @throws InvalidFormatException
	 * @throws IOException
	 * @throws InstantiationException
	 * @throws IllegalAccessException
	 */
	public static void readFilename(final String filename, final List<ExcelConfig> excelConfigList) throws InvalidFormatException, IOException, InstantiationException, IllegalAccessException {
		ExcelUtils.readFile(new File(filename), excelConfigList);
	}

	/**
	 * 读取文件流
	 *
	 * @param is
	 *          InputStream
	 * @param excelConfigList
	 *          excel配置集合
	 * @return
	 * @throws InvalidFormatException
	 * @throws IOException
	 * @throws InstantiationException
	 * @throws IllegalAccessException
	 */
	public static void readInputStream(final InputStream is, final List<ExcelConfig> excelConfigList) throws InvalidFormatException, IOException, InstantiationException, IllegalAccessException {
		final Workbook workbook = WorkbookFactory.create(is);

		ExcelUtils.readWorkbookForExcelConfigs(workbook, excelConfigList);
	}

	/**
	 * 读取行
	 *
	 * @param row
	 *          行
	 * @param columns
	 *          列定义
	 * @return
	 * @throws InstantiationException
	 * @throws IllegalAccessException
	 */
	public static Map<String, Object> readRow(final Row row, final Map<Integer, String> columns) throws InstantiationException, IllegalAccessException {
		final Map<String, Object> map = Maps.newHashMap();

		for (final Entry<Integer, String> columnEntry : columns.entrySet()) {
			final Cell cell = row.getCell(columnEntry.getKey());

			if (cell == null) {
				map.put(columnEntry.getValue(), null);

				continue;
			}

			final int type = cell.getCellType();

			if (type == Cell.CELL_TYPE_BOOLEAN) {
				map.put(columnEntry.getValue(), cell.getBooleanCellValue());
			} else if (type == Cell.CELL_TYPE_NUMERIC) {
				map.put(columnEntry.getValue(), cell.getNumericCellValue());
			} else {
				map.put(columnEntry.getValue(), cell.getStringCellValue());
			}
		}

		return map;
	}

	/**
	 * 读取Sheet
	 *
	 * @param sheet
	 *          Sheet
	 * @param excelConfig
	 *          excel配置
	 * @throws InstantiationException
	 * @throws IllegalAccessException
	 */
	public static void readSheet(final Sheet sheet, final ExcelConfig excelConfig) throws InstantiationException, IllegalAccessException {
		int rowIndex = 0;

		do {
			final int rownum = (rowIndex++) + excelConfig.getFirstRowIndex();

			final Row row = sheet.getRow(rownum);

			if (row == null) {
				return;
			}

			Map<String, Object> rowData = null;

			if (excelConfig.getReadRowFunction() == null) {
				rowData = ExcelUtils.readRow(row, excelConfig.getColumns());
			} else {
				rowData = excelConfig.getReadRowFunction().execution(row, rownum, excelConfig.getColumns());
			}

			if (excelConfig.getDataList() == null) {
				excelConfig.setDataList(Lists.<Map<String, Object>> newArrayList());
			}

			excelConfig.getDataList().add(rowData);

			if (excelConfig.getValidateData() == null) {
				continue;
			}

			if (BooleanUtils.isTrue(excelConfig.getValidateData().execution(rowData))) {
				if (excelConfig.getSucceedDataList() == null) {
					excelConfig.setSucceedDataList(Lists.<Map<String, Object>> newArrayList());
				}

				excelConfig.getSucceedDataList().add(rowData);

				continue;
			}

			if (excelConfig.getErrorDataList() == null) {
				excelConfig.setErrorDataList(Lists.<Map<String, Object>> newArrayList());
			}

			excelConfig.getErrorDataList().add(rowData);
		} while (true);
	}

	/**
	 * 读取工作薄
	 *
	 * @param workbook
	 *          工作薄
	 * @param excelConfig
	 *          excel配置
	 * @throws InstantiationException
	 * @throws IllegalAccessException
	 */
	public static void readWorkbookForExcelConfig(final Workbook workbook, final ExcelConfig excelConfig) throws InstantiationException, IllegalAccessException {
		if (ArrayUtils.isNotEmpty(excelConfig.getSheetIndexs())) {
			for (final int sheetIndex : excelConfig.getSheetIndexs()) {
				final Sheet sheet = workbook.getSheetAt(sheetIndex);

				if (excelConfig.getAction() != null) {
					excelConfig.getAction().run(sheet);
				}

				ExcelUtils.readSheet(sheet, excelConfig);
			}

			return;
		}

		if (ArrayUtils.isEmpty(excelConfig.getSheetNames())) {
			return;
		}

		for (final String sheetName : excelConfig.getSheetNames()) {
			final Sheet sheet = workbook.getSheet(sheetName);

			if (excelConfig.getAction() != null) {
				excelConfig.getAction().run(sheet);
			}

			ExcelUtils.readSheet(sheet, excelConfig);
		}
	}

	/**
	 * 读取工作薄
	 *
	 * @param workbook
	 *          工作薄
	 * @param excelConfigList
	 *          excel配置集合
	 * @return
	 * @throws InstantiationException
	 * @throws IllegalAccessException
	 */
	public static void readWorkbookForExcelConfigs(final Workbook workbook, final List<ExcelConfig> excelConfigList) throws InstantiationException, IllegalAccessException {
		for (final ExcelConfig excelConfig : excelConfigList) {
			ExcelUtils.readWorkbookForExcelConfig(workbook, excelConfig);
		}
	}

	/**
	 * 写入文件
	 *
	 * @param file
	 *          文件
	 * @param excelConfigList
	 *          excel配置集合
	 * @throws InvalidFormatException
	 * @throws IOException
	 * @throws InstantiationException
	 * @throws IllegalAccessException
	 */
	public static void writeFile(final File file, final List<ExcelConfig> excelConfigList) throws InvalidFormatException, IOException, InstantiationException, IllegalAccessException {
		final Workbook workbook = WorkbookFactory.create(file);

		ExcelUtils.writeWorkbookForExcelConfigs(workbook, excelConfigList);
	}

	/**
	 * 写入文件名
	 *
	 * @param filename
	 *          文件名
	 * @param excelConfigList
	 *          excel配置集合
	 * @throws InvalidFormatException
	 * @throws IOException
	 * @throws InstantiationException
	 * @throws IllegalAccessException
	 */
	public static void writeFilename(final String filename, final List<ExcelConfig> excelConfigList) throws InvalidFormatException, IOException, InstantiationException, IllegalAccessException {
		ExcelUtils.writeFile(new File(filename), excelConfigList);
	}

	/**
	 * 写入流
	 *
	 * @param response
	 * @param downFilename
	 *          下载文件名
	 * @param templateFileName
	 *          模板文件名称
	 * @param excelConfigList
	 *          excel配置集合
	 * @throws Exception
	 */
	public static void writeHttpServletResponse(final HttpServletResponse response, final String downFilename, final String templateFileName, final List<ExcelConfig> excelConfigList) throws Exception {
		response.setContentType("application/x-msdownload");
		response.setHeader("content-disposition", "attachment; filename=" + URLEncoder.encode(downFilename, "UTF-8"));

		final Workbook templateWorkbook = WorkbookFactory.create(new File(templateFileName));

		final Workbook workbook = new XSSFWorkbook();

		for (final ExcelConfig excelConfig : excelConfigList) {
			for (final int sheetIndex : excelConfig.getSheetIndexs()) {
				final Sheet templateheet = templateWorkbook.getSheetAt(sheetIndex);

				if (templateheet == null) {
					continue;
				}

				final Sheet sheet = workbook.createSheet(templateheet.getSheetName());

				ExcelUtils.copySheet(templateheet, sheet);

				ExcelUtils.writeSheet(sheet, excelConfig);
			}
		}

		workbook.write(response.getOutputStream());
	}

	/**
	 * 写入文件流
	 *
	 * @param is
	 *          InputStream
	 * @param excelConfigList
	 *          excel配置集合
	 * @return
	 * @throws InvalidFormatException
	 * @throws IOException
	 * @throws InstantiationException
	 * @throws IllegalAccessException
	 */
	public static void writeInputStream(final InputStream is, final List<ExcelConfig> excelConfigList) throws InvalidFormatException, IOException, InstantiationException, IllegalAccessException {
		final Workbook workbook = WorkbookFactory.create(is);

		ExcelUtils.writeWorkbookForExcelConfigs(workbook, excelConfigList);
	}

	/**
	 * 写入行
	 *
	 * @param row
	 *          行
	 * @param columns
	 *          列定义
	 * @return
	 * @throws InstantiationException
	 * @throws IllegalAccessException
	 */
	public static Map<String, Object> writeRow(final Row row, final Map<Integer, String> columns) throws InstantiationException, IllegalAccessException {
		final Map<String, Object> map = Maps.newHashMap();

		for (final Entry<Integer, String> columnEntry : columns.entrySet()) {
			final Cell cell = row.getCell(columnEntry.getKey());

			if (cell == null) {
				map.put(columnEntry.getValue(), null);

				continue;
			}

			final int type = cell.getCellType();

			if (type == Cell.CELL_TYPE_BOOLEAN) {
				map.put(columnEntry.getValue(), cell.getBooleanCellValue());
			} else if (type == Cell.CELL_TYPE_NUMERIC) {
				map.put(columnEntry.getValue(), cell.getNumericCellValue());
			} else {
				map.put(columnEntry.getValue(), cell.getStringCellValue());
			}
		}

		return map;
	}

	/**
	 * 写入Sheet
	 *
	 * @param sheet
	 *          Sheet
	 * @param excelConfig
	 *          excel配置
	 * @throws InstantiationException
	 * @throws IllegalAccessException
	 */
	public static void writeSheet(final Sheet sheet, final ExcelConfig excelConfig) throws InstantiationException, IllegalAccessException {
		if (CollectionUtils.isEmpty(excelConfig.getDataList())) {
			return;
		}

		int rowIndex = 0;

		for (final Map<String, Object> rowData : excelConfig.getDataList()) {
			final int rownum = (rowIndex++) + excelConfig.getFirstRowIndex();

			final Row row = sheet.createRow(rownum);

			for (final Entry<Integer, String> columnEntry : excelConfig.getColumns().entrySet()) {
				final Object value = rowData.get(columnEntry.getValue());

				if (value == null) {
					continue;
				}

				final Cell cell = row.createCell(columnEntry.getKey());

				if (value instanceof Double) {
					cell.setCellValue((Double) value);
				} else if (value instanceof BigDecimal) {
					cell.setCellValue(((BigDecimal) value).toString());
				} else if (value instanceof Date) {
					cell.setCellValue((Date) value);
				} else if (value instanceof Integer) {
					cell.setCellValue((Integer) value);
				} else if (value instanceof Boolean) {
					cell.setCellValue((Boolean) value);
				} else if (value instanceof Calendar) {
					cell.setCellValue((Calendar) value);
				} else {
					cell.setCellValue(String.valueOf(value));
				}
			}
		}
	}

	/**
	 * 写入工作薄
	 *
	 * @param workbook
	 *          工作薄
	 * @param excelConfig
	 *          excel配置
	 * @throws InstantiationException
	 * @throws IllegalAccessException
	 */
	public static void writeWorkbookForExcelConfig(final Workbook workbook, final ExcelConfig excelConfig) throws InstantiationException, IllegalAccessException {
		if (ArrayUtils.isNotEmpty(excelConfig.getSheetIndexs())) {
			for (final int sheetIndex : excelConfig.getSheetIndexs()) {
				final Sheet sheet = workbook.getSheetAt(sheetIndex);

				if (excelConfig.getAction() != null) {
					excelConfig.getAction().run(sheet);
				}

				ExcelUtils.writeSheet(sheet, excelConfig);
			}

			return;
		}

		if (ArrayUtils.isEmpty(excelConfig.getSheetNames())) {
			return;
		}

		for (final String sheetName : excelConfig.getSheetNames()) {
			final Sheet sheet = workbook.getSheet(sheetName);

			if (excelConfig.getAction() != null) {
				excelConfig.getAction().run(sheet);
			}

			ExcelUtils.writeSheet(sheet, excelConfig);
		}
	}

	/**
	 * 写入工作薄
	 *
	 * @param workbook
	 *          工作薄
	 * @param excelConfigList
	 *          excel配置集合
	 * @return
	 * @throws InstantiationException
	 * @throws IllegalAccessException
	 */
	public static void writeWorkbookForExcelConfigs(final Workbook workbook, final List<ExcelConfig> excelConfigList) throws InstantiationException, IllegalAccessException {
		for (final ExcelConfig excelConfig : excelConfigList) {
			ExcelUtils.writeWorkbookForExcelConfig(workbook, excelConfig);
		}
	}
}
